import os
import sqlite3
import polars as pl


class SQLite:
    def __init__(self, dbpath):
        if not os.path.isabs(dbpath):
            raise ValueError("数据库文件路径必须是绝对路径。")
        self.dbpath = dbpath
        with sqlite3.connect(self.dbpath) as conn:
            self._create_tables(conn)

    @staticmethod
    def get_table_schemas():
        return {
            "CertificateCode": """
                   CREATE TABLE IF NOT EXISTS CertificateCode (
                       id INTEGER PRIMARY KEY AUTOINCREMENT,
                       Item TEXT NOT NULL,
                       CertificateCode TEXT NOT NULL,
                       battCode TEXT
                   )
               """
        }

    def create_table_if_not_exists(self, conn, table_name, schema):
        cursor = conn.cursor()
        cursor.execute(schema)

    def _create_tables(self, conn):
        for table_name, schema in self.get_table_schemas().items():
            self.create_table_if_not_exists(conn, table_name, schema)

    def connect(self):
        return sqlite3.connect(self.dbpath)

    # 查询,这个方法将接受一个SQL查询和一个值作为参数,value参数是可选的，将用于参数化查询
    # 并返回一个Polars DataFrame。
    # 如果没有提供值,则返回所有行。
    def query(self, sql, value=None):
        with self.connect() as conn:
            if value is None:
                return pl.read_database(
                    query=sql,
                    connection=conn,
                    schema_overrides={"normalised_score": pl.UInt8},
                )
            else:
                return pl.read_database(
                    query=sql,
                    connection=conn,
                    execute_options={"parameters": {"value": value}},
                )


if __name__ == "__main__":
    sqlite = SQLite('D:\\apply.db')
    # 带参数查询
    # df = sqlite.query("SELECT id,Item,CertificateCode,battCode FROM CertificateCode WHERE CertificateCode=:value",
    #                   'PEKSZ202311203176')
    # 不带参数查询
    df = sqlite.query("SELECT id,Item,CertificateCode,battCode FROM CertificateCode")

    print(df)